<?php
class ModelCatalogManufacturer extends Model {
	public function getManufacturer($manufacturer_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "manufacturer m LEFT JOIN " . DB_PREFIX . "manufacturer_to_store m2s ON (m.manufacturer_id = m2s.manufacturer_id) WHERE m.manufacturer_id = '" . (int)$manufacturer_id . "' AND m2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
	
		return $query->row;	
	}
	
	public function getManufacturers() {
		$manufacturer = $this->cache->get('manufacturer.' . (int)$this->config->get('config_store_id'));
		
		if (!$manufacturer) {
            $sql = "SELECT * FROM " . DB_PREFIX . "manufacturer m " .
                " LEFT JOIN " . DB_PREFIX . "manufacturer_to_store m2s ON (m.manufacturer_id = m2s.manufacturer_id) " . 
                " WHERE m2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND m.status = 1 " .
                " ORDER BY sort_order, LCASE(m.name) ASC";
                
			$query = $this->db->query($sql);
	
			$manufacturer = $query->rows;    			
			$this->cache->set('manufacturer.' . (int)$this->config->get('config_store_id'), $manufacturer);
		}
		
		return $manufacturer;
	} 
    
    public function getManufacturersByCategory($category_id) {
        $manufacturer = $this->cache->get('manufacturer.category.' . (int)$this->config->get('config_store_id') . '_' . (int)$category_id);
        
        if (!$manufacturer) {
            $categories = $this->getCategoriesList($category_id);
            
			/*
            $sql = "SELECT DISTINCT m.* FROM " . DB_PREFIX . "manufacturer m ".
                " LEFT JOIN " . DB_PREFIX . "manufacturer_to_store m2s ON (m.manufacturer_id = m2s.manufacturer_id)" . 
                " LEFT JOIN " . DB_PREFIX . "product p ON (p.manufacturer_id = m.manufacturer_id)" . 
				//" LEFT JOIN stock_status ss ON (p.stock_status_id = ss.stock_status_id) ".
                " LEFT JOIN " . DB_PREFIX . "product_to_category pc ON (pc.product_id = p.product_id)" .
                " LEFT JOIN category c ON (c.category_id = pc.category_id)" .
                //" WHERE m2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND (pc.category_id = '" . (int)$category_id . "' OR c.parent_id = '" . (int)$category_id . "')" .
                " WHERE m2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND (pc.category_id IN (" . $categories . ") OR c.parent_id = '" . (int)$category_id . "') AND m.status = 1" .
                " ORDER BY m.sort_order, LCASE(m.name) ASC";
			*/
			$sql = "SELECT DISTINCT m.* FROM manufacturer m ". 
				" LEFT JOIN manufacturer_to_store m2s ON (m.manufacturer_id = m2s.manufacturer_id) ". 
				" INNER JOIN ( ".
				"	SELECT p.product_id, p.manufacturer_id ".
				"	FROM (SELECT product_id, manufacturer_id FROM product WHERE quantity > 0) p ".
				"	LEFT JOIN product_to_category pc ON (pc.product_id = p.product_id) ". 				
				"	LEFT JOIN category c ON (c.category_id = pc.category_id) ".
				"	WHERE pc.category_id IN (" . $categories . ") OR c.parent_id = '" . (int)$category_id . "' ".
				" ) p ON (p.manufacturer_id = m.manufacturer_id) ".
				//" 	INNER JOIN stock_status ss ON (p.stock_status_id = ss.stock_status_id) ".
				" WHERE m.status = 1  AND m2s.store_id = '" . (int)$this->config->get('config_store_id') . "' ".
				" ORDER BY m.sort_order, LCASE(m.name) ASC ";

            $query = $this->db->query($sql);
            //echo $sql;
            $manufacturer = $query->rows;            
            $this->cache->set('manufacturer.category.' . (int)$this->config->get('config_store_id') . '_' . (int)$category_id, $manufacturer);
        }
              
        return $manufacturer;
    } 
    
    private function getCategoriesList($category_id){
        $sql = "SELECT category_id FROM category WHERE status = '1' AND parent_id = '" . (int)$category_id . "'";
        $query = $this->db->query($sql); 
        
        $rows = array($category_id);
        $level1 = array();
        $categories = $query->rows;
        foreach($categories as $row){
            $rows[] = $row['category_id'];
            $level1[] = $row['category_id']; 
        }
       
        if(!empty($level1)){            
            $sql = "SELECT category_id FROM category WHERE status = '1' AND parent_id IN (" . implode(',', $level1) . ")";
            
            $query = $this->db->query($sql); 
            $level2 = $query->rows; 
            foreach($level2 as $row_item){
                $rows[] = $row_item['category_id'];                
            }            
        } 
                                                   
        $list = implode(',', $rows);            
        
        return $list;
    }
    
    public function getManufacturersPath() {
        //$manufacturers = $this->cache->get('manufacturer.home.' . (int)$this->config->get('config_store_id'));
        $manufacturers = null;
        
        if (!$manufacturers) {
            $manufacturers = array();
            $manufacturers_data = $this->getManufacturers();
            foreach($manufacturers_data as $item){                
                $sql = "SELECT DISTINCT c.category_id, c.sort_order FROM " . DB_PREFIX . "product p " .                                
                    " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) " .
                    " LEFT JOIN " . DB_PREFIX . "category c ON (p2c.category_id = c.category_id) " .
                    " WHERE p.manufacturer_id = '" . (int)$item['manufacturer_id'] . "' " .
                    " ORDER BY c.sort_order ASC";
                $query = $this->db->query($sql);
                
                if(isset($query->row['category_id'])){
                    $path = $this->getParentCategoryPath ($query->row['category_id']);  
                    $path = explode('_', $path);
                    $path = $path[0];
                } 
                else{
                    $path = 0;  
                }
                
                $manufacturers[] = array(
                    'manufacturer_id'   => $item['manufacturer_id'],
                    'name'              => $item['name'],
                    'image'             => $item['logo'],
                    'category'          => isset($query->row['category_id']) ? $query->row['category_id'] : 0,
                    'path'              => $path,
                );
            }
                                    
            //$this->cache->set('manufacturer.home.' . (int)$this->config->get('config_store_id'), $manufacturers);
        }
          
        return $manufacturers;
    }
    
    public function getParentCategoryPath($category_id) {        
        $path = '';
        $query = $this->db->query("SELECT parent_id FROM " . DB_PREFIX . "category WHERE category_id = '" . (int)$category_id . "'"); 
        
        if(!empty($query->row) && $query->row['parent_id'] > 0){
            $path = $this->getParentCategoryPath($query->row['parent_id']);        
        } 
        
        $path .= $category_id . '_';     
        
        return $path;
    } 
    
    public function getProductsByManufacturer($manufacturer_id, $sort = 'pms.product_msort_order', $order = 'ASC', $start = 0, $limit = 20) {        
        $sql = "SELECT DISTINCT p.*, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, pms.product_msort_order" . //pcs.product_sort_order" .            
            " FROM " . DB_PREFIX . "product p " .
            " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)" .
            " LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)" .
            " LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id)" .
            " LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id)" .
            " LEFT JOIN " . DB_PREFIX . "product_to_category pc ON (p.product_id = pc.product_id)" .
            " LEFT JOIN " . DB_PREFIX . "category_description cd ON (pc.category_id = cd.category_id)" .
            //" LEFT JOIN " . DB_PREFIX . "product_category_sort pcs ON (pcs.product_id = p.product_id AND pcs.category_id = pc.category_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_manufacturer_sort pms ON (pms.product_id = p.product_id AND pms.manufacturer_id = p.manufacturer_id AND IFNULL(pms.category_id, 0) = '0') " .
            " WHERE p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'" . 
            " AND m.manufacturer_id = '" . (int)$manufacturer_id. "' AND cd.language_id = '" . (int)$this->config->get('config_language_id'). "'";

        $sort_data = array(
            'pd.name',
            //'p.sort_order',            
            'pms.product_msort_order',
            'p.price',
            'p.date_added',
            'p.model',
            'cd.name',
        );
            
        if (in_array($sort, $sort_data)) {
            if ($sort == 'pd.name' || $sort == 'p.model' || $sort == 'cd.name') {
                $sql .= " ORDER BY LCASE(" . $sort . ")";
            } else {
                $sql .= " ORDER BY " . $sort;
            }
        } else {
            $sql .= " ORDER BY p.sort_order";    
        }
            
        if ($order == 'DESC') {
            $sql .= " DESC";
        } else {
            $sql .= " ASC";
        }

        if ($start < 0) {
            $start = 0;
        }
        
        $sql .= " LIMIT " . (int)$start . "," . (int)$limit;
        
        $query = $this->db->query($sql);
        
        return $query->rows;
    } 

    public function getTotalProductsByManufacturer($manufacturer_id) {       
        $sql = "SELECT COUNT(pp.product_id) AS total FROM (" . 
            "   SELECT DISTINCT p.product_id FROM " . DB_PREFIX . "product p " .
            "   LEFT JOIN product_to_category pc ON (p.product_id = pc.product_id)" . 
            "   WHERE p.status = '1' AND p.date_available <= NOW() AND p.manufacturer_id = '" . (int)$manufacturer_id . "'" .
            ") pp";
        
        $query = $this->db->query($sql);
        
        return $query->row['total'];
    }
    
    public function getManufacturerMetaTitle($manufacturer_id, $category_id) {       
        $sql = "SELECT * FROM " . DB_PREFIX . "manufacturer_category_meta_title WHERE manufacturer_id = '" . (int)$manufacturer_id . "' AND category_id = '" . (int)$category_id . "'";        
        $query = $this->db->query($sql);
        
        return !empty($query->row) ? $query->row['meta_title'] : '';
    }
}
?>